﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Utility;
using Entity;
using DataAccess.IService;

namespace DataAccess.Service
{
    public class GuestbookService:IGuestbookService
    {
        public DataTable getGuestbookTable()
        {
            string CommandText = "select * from GuestBook";
            string[] colNames = new string[]
            {
                "EntityId",
                "Title",
                "Author",
                "EntityType",
                "EntityStatus",
                "CreateDate",
                "lastReplyDate",
                "TotalPosts",
                "TotalViews"
            };
            return SQLHelper.generateDataTable(CommandText, colNames);
        }



        public GuestBook getGuestbookDetail(string entityid)
        {

            GuestBook gb = new GuestBook();
            string commandText = string.Format("select * from GuestBook where EntityId = @EntityId"); // 查询sql语句
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@EntityId",entityid)
            };

            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                gb.Title = sdReader["Title"].ToString();
                gb.Body = sdReader["Body"].ToString();
                gb.Author = sdReader["Author"].ToString();
                gb.EntityType = sdReader["EntityType"].ToString();
                gb.EntityStatus = sdReader["EntityStatus"].ToString();
                gb.CreateDate = (DateTime)sdReader["CreateDate"];
                gb.UpdateDate = (DateTime)sdReader["UpdateDate"];
                gb.StickDate = (DateTime)sdReader["StickDate"];
                gb.TotalPosts = Convert.ToInt32(sdReader["TotalPosts"].ToString());
                gb.TotalViews = Convert.ToInt32(sdReader["TotalViews"].ToString());
                gb.RecentReplierName = sdReader["RecentReplierName"].ToString();
                gb.lastReplyDate = (DateTime)sdReader["lastReplyDate"];
            }
            return gb;
        }

        public void addNewMsg(GuestBook guestbook)
        {
            string commandText = string.Format("insert into GuestBook(Title,Body,Author,CreateDate,UpdateDate,StickDate,lastReplyDate,TotalPosts,TotalViews,EntityStatus) values(@Title,@Body,@Author,@CreateDate,@UpdateDate,@StickDate,@lastReplyDate,@TotalPosts,@TotalViews,@EntityStatus)"); // 查询sql语句
            DateTime dt = DateTime.Now;
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@Title",guestbook.Title),
                new SqlParameter("@Body",guestbook.Body),
                new SqlParameter("@Author",guestbook.Author),
                new SqlParameter("@CreateDate",dt),
                new SqlParameter("@UpdateDate",dt),
                new SqlParameter("@StickDate",dt),
                new SqlParameter("@lastReplyDate",dt),
                new SqlParameter("@TotalPosts",Convert.ToInt32(0)),
                new SqlParameter("@TotalViews",Convert.ToInt32(0)),
                new SqlParameter("@EntityStatus",Convert.ToString("未处理"))
            };
            SQLHelper.ExecuteNonQuery(commandText, paras);
        }





        public DataTable getGuestbookReplys(string entityid)
        {
            string CommandText = "select * from GuestBookReply";
            string[] colNames = new string[]
            {
                "ReplyId",
                "EntityId",
                "Body",
                "ReplierName",
                "ReplierDate"
            };
            return SQLHelper.generateDataTable(CommandText, colNames);

        }


        public void addNewReply(GuestBookReply gbr)
        {
            string commandText = string.Format("insert into GuestBookReply(EntityId,Body,ReplierName,ReplierDate) values(@EntityId,@Body,@ReplierName,@ReplierDate)"); // 查询sql语句
            DateTime dt = DateTime.Now;
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@EntityId",gbr.EntityId),
                new SqlParameter("@Body",gbr.Body),
                new SqlParameter("@ReplierName",gbr.ReplierName),
                new SqlParameter("@ReplierDate",dt)
            };
            SQLHelper.ExecuteNonQuery(commandText, paras);
        }



        public bool replyEntity(string entityid,string body)
        {
            bool result = false;
            string commandText = string.Format("update GuestBook set AdminReply = @AdminReply where EntityId = @EntityId"); // 查询sql语句
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@EntityId",entityid),
                new SqlParameter("@AdminReply",body),
            };

            if (SQLHelper.ExecuteNonQuery(commandText, paras)>0)
            {
                result = true;
            }
            return result;
        }



        public bool markSolved(string entityid)
        {
            bool result = false;
            string commandText = string.Format("update GuestBook set EntityStatus = @EntityStatus where EntityId = @EntityId"); // 查询sql语句
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@EntityId",entityid),
                new SqlParameter("@EntityStatus",Convert.ToString("已解决"))
            };

            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;


        }

        public int getUnsolvedNum()
        {
            int result = 0;
            string commandText = string.Format("select COUNT(*) from GuestBook where EntityStatus = '未处理'"); // 查询sql语句
            result = (int)SQLHelper.ExecuteScalar(commandText);
            return result;
        }

        public bool delEntity(string entityid)
        {
            bool result = false;

            string commandText = string.Format("delete GuestBook where EntityId = @EntityId"); // 查询sql语句
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@EntityId",entityid)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras)>0)
            {
                return true;
            }
            return result;
        }
    }
}
